{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "A \"Single Item Order\" is a customer order where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>salesorderid</th>\n",
       "        <th>unitprice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71776</td>\n",
       "        <td>63.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>32.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>48.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>72.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>113.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>809.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>14.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>32.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>63.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>200.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71783</td>\n",
       "        <td>5.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71783</td>\n",
       "        <td>72.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71783</td>\n",
       "        <td>356.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71784</td>\n",
       "        <td>200.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71784</td>\n",
       "        <td>445.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71784</td>\n",
       "        <td>602.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71796</td>\n",
       "        <td>31.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71796</td>\n",
       "        <td>54.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71796</td>\n",
       "        <td>728.91</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">82 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(71776, 63.9),\n",
       " (71780, 32.99),\n",
       " (71780, 48.59),\n",
       " (71780, 72.16),\n",
       " (71780, 113.0),\n",
       " (71780, 809.76),\n",
       " (71782, 14.69),\n",
       " (71782, 31.58),\n",
       " (71782, 32.39),\n",
       " (71782, 63.9),\n",
       " (71782, 200.05),\n",
       " (71783, 5.39),\n",
       " (71783, 72.16),\n",
       " (71783, 356.9),\n",
       " (71784, 200.05),\n",
       " (71784, 445.41),\n",
       " (71784, 602.35),\n",
       " (71796, 31.58),\n",
       " (71796, 54.94),\n",
       " (71796, 728.91),\n",
       " (71796, 1430.44),\n",
       " (71797, 29.99),\n",
       " (71797, 38.1),\n",
       " (71797, 202.33),\n",
       " (71797, 858.9),\n",
       " (71815, 202.33),\n",
       " (71816, 63.9),\n",
       " (71816, 445.41),\n",
       " (71816, 728.91),\n",
       " (71816, 1430.44),\n",
       " (71831, 1376.99),\n",
       " (71832, 16.27),\n",
       " (71832, 26.72),\n",
       " (71832, 31.58),\n",
       " (71832, 37.15),\n",
       " (71832, 37.25),\n",
       " (71832, 41.99),\n",
       " (71832, 48.59),\n",
       " (71832, 72.16),\n",
       " (71832, 149.87),\n",
       " (71832, 158.43),\n",
       " (71832, 461.69),\n",
       " (71832, 818.7),\n",
       " (71845, 26.72),\n",
       " (71845, 37.15),\n",
       " (71845, 54.89),\n",
       " (71845, 242.99),\n",
       " (71845, 818.7),\n",
       " (71846, 113.0),\n",
       " (71846, 818.7),\n",
       " (71856, 54.89),\n",
       " (71856, 445.41),\n",
       " (71858, 4.77),\n",
       " (71858, 12.14),\n",
       " (71858, 54.89),\n",
       " (71863, 20.99),\n",
       " (71863, 1020.59),\n",
       " (71867, 858.9),\n",
       " (71897, 38.1),\n",
       " (71897, 63.9),\n",
       " (71897, 1430.44),\n",
       " (71898, 12.14),\n",
       " (71898, 63.9),\n",
       " (71898, 602.35),\n",
       " (71899, 26.72),\n",
       " (71899, 48.59),\n",
       " (71899, 113.0),\n",
       " (71899, 461.69),\n",
       " (71899, 809.76),\n",
       " (71902, 31.58),\n",
       " (71902, 37.25),\n",
       " (71902, 72.16),\n",
       " (71902, 218.45),\n",
       " (71915, 48.59),\n",
       " (71920, 72.0),\n",
       " (71923, 2.99),\n",
       " (71935, 24.29),\n",
       " (71935, 323.99),\n",
       " (71936, 20.99),\n",
       " (71936, 113.0),\n",
       " (71936, 149.87),\n",
       " (71936, 158.43)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SalesOrderID, UnitPrice\n",
    "  FROM SalesOrderDetail\n",
    "    WHERE OrderQty=1\n",
    "    GROUP BY SalesOrderID, UnitPrice\n",
    "    HAVING COUNT(*)=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Where did the racing socks go? List the product name and the CompanyName for all Customers who ordered ProductModel 'Racing Socks'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>companyname</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eastside Department Store</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Essential Bike Works</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Remarkable Bike Store</td>\n",
       "        <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Remarkable Bike Store</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riding Cycles</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sports Products Store</td>\n",
       "        <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sports Products Store</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Bicycle Accessories Company</td>\n",
       "        <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Bicycle Accessories Company</td>\n",
       "        <td>Racing Socks, L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thrifty Parts and Sales</td>\n",
       "        <td>Racing Socks, M</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Eastside Department Store', 'Racing Socks, L'),\n",
       " ('Essential Bike Works', 'Racing Socks, L'),\n",
       " ('Remarkable Bike Store', 'Racing Socks, M'),\n",
       " ('Remarkable Bike Store', 'Racing Socks, L'),\n",
       " ('Riding Cycles', 'Racing Socks, L'),\n",
       " ('Sports Products Store', 'Racing Socks, M'),\n",
       " ('Sports Products Store', 'Racing Socks, L'),\n",
       " ('The Bicycle Accessories Company', 'Racing Socks, M'),\n",
       " ('The Bicycle Accessories Company', 'Racing Socks, L'),\n",
       " ('Thrifty Parts and Sales', 'Racing Socks, M')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT CompanyName, Product.Name\n",
    "  FROM Product JOIN SalesOrderDetail ON (\n",
    "        Product.ProductID=SalesOrderDetail.ProductID) JOIN\n",
    "    SalesOrderHeader ON (\n",
    "        SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID) JOIN\n",
    "    CustomerAW ON (\n",
    "        CustomerAW.CustomerID=SalesOrderHeader.CustomerID) JOIN\n",
    "    ProductModel ON (\n",
    "        ProductModel.ProductModelID=Product.ProductModelID)\n",
    "    WHERE ProductModel.Name='Racing Socks'\n",
    "    ORDER BY CompanyName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Show the product description for culture 'fr' for product with ProductID 736."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Le cadre LL en aluminium offre une conduite confortable, une excellente absorption des bosses pour un tr?s bon rapport qualit?-prix.</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Le cadre LL en aluminium offre une conduite confortable, une excellente absorption des bosses pour un tr?s bon rapport qualit?-prix.',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Description\n",
    "  FROM Product JOIN ProductModelProductDescription ON (\n",
    "    Product.ProductModelID=ProductModelProductDescription.ProductModelID) JOIN\n",
    "    ProductDescription ON (\n",
    "        ProductDescription.ProductDescriptionID=\n",
    "            ProductModelProductDescription.ProductDescriptionID)\n",
    "    WHERE Product.ProductID=736 AND ProductModelProductDescription.Culture='fr'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "Use the SubTotal value in SaleOrderHeader to list orders from the largest to the smallest. For each order show the CompanyName and the SubTotal and the total weight of the order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>companyname</th>\n",
       "        <th>subtotal</th>\n",
       "        <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Action Bicycle Specialists</td>\n",
       "        <td>108561.83</td>\n",
       "        <td>1133911.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Metropolitan Bicycle Supply</td>\n",
       "        <td>98278.69</td>\n",
       "        <td>679588.0200000003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bulk Discount Store</td>\n",
       "        <td>88812.86</td>\n",
       "        <td>34813.049999999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eastside Department Store</td>\n",
       "        <td>83858.43</td>\n",
       "        <td>565638.7199999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riding Cycles</td>\n",
       "        <td>78029.69</td>\n",
       "        <td>504095.32999999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Many Bikes Store</td>\n",
       "        <td>74058.81</td>\n",
       "        <td>744328.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Instruments and Parts Company</td>\n",
       "        <td>63980.99</td>\n",
       "        <td>731576.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Extreme Riding Supplies</td>\n",
       "        <td>57634.63</td>\n",
       "        <td>589939.1100000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trailblazing Sports</td>\n",
       "        <td>41622.05</td>\n",
       "        <td>234328.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Professional Sales and Service</td>\n",
       "        <td>39785.33</td>\n",
       "        <td>396843.6299999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nearby Cycle Shop</td>\n",
       "        <td>38418.69</td>\n",
       "        <td>547260.4700000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Closest Bicycle Store</td>\n",
       "        <td>35775.21</td>\n",
       "        <td>340144.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thrilling Bike Tours</td>\n",
       "        <td>13823.71</td>\n",
       "        <td>191855.75999999998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paints and Solvents Company</td>\n",
       "        <td>12685.89</td>\n",
       "        <td>122609.42000000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Remarkable Bike Store</td>\n",
       "        <td>6634.3</td>\n",
       "        <td>45103.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Engineered Bike Systems</td>\n",
       "        <td>3398.17</td>\n",
       "        <td>37420.659999999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sports Products Store</td>\n",
       "        <td>3324.28</td>\n",
       "        <td>53389.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Discount Tours</td>\n",
       "        <td>2980.79</td>\n",
       "        <td>14977.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sports Store</td>\n",
       "        <td>2453.76</td>\n",
       "        <td>38354.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Coalition Bike Company</td>\n",
       "        <td>2415.67</td>\n",
       "        <td>29182.999999999996</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Action Bicycle Specialists', 108561.83, 1133911.56),\n",
       " ('Metropolitan Bicycle Supply', 98278.69, 679588.0200000003),\n",
       " ('Bulk Discount Store', 88812.86, 34813.049999999996),\n",
       " ('Eastside Department Store', 83858.43, 565638.7199999999),\n",
       " ('Riding Cycles', 78029.69, 504095.32999999996),\n",
       " ('Many Bikes Store', 74058.81, 744328.6),\n",
       " ('Instruments and Parts Company', 63980.99, 731576.77),\n",
       " ('Extreme Riding Supplies', 57634.63, 589939.1100000001),\n",
       " ('Trailblazing Sports', 41622.05, 234328.12),\n",
       " ('Professional Sales and Service', 39785.33, 396843.6299999999),\n",
       " ('Nearby Cycle Shop', 38418.69, 547260.4700000001),\n",
       " ('Closest Bicycle Store', 35775.21, 340144.28),\n",
       " ('Thrilling Bike Tours', 13823.71, 191855.75999999998),\n",
       " ('Paints and Solvents Company', 12685.89, 122609.42000000001),\n",
       " ('Remarkable Bike Store', 6634.3, 45103.5),\n",
       " ('Engineered Bike Systems', 3398.17, 37420.659999999996),\n",
       " ('Sports Products Store', 3324.28, 53389.08),\n",
       " ('Discount Tours', 2980.79, 14977.56),\n",
       " ('Sports Store', 2453.76, 38354.65),\n",
       " ('Coalition Bike Company', 2415.67, 29182.999999999996),\n",
       " ('Aerobic Exercise Company', 2137.23, 6770.4400000000005),\n",
       " ('Tachometers and Accessories', 2016.34, 10591.33),\n",
       " ('Thrifty Parts and Sales', 1141.58, 3175.14),\n",
       " ('Vigorous Sports Store', 1059.31, 1043.26),\n",
       " ('Good Toys', 880.35, 2050.23),\n",
       " ('Transport Bikes', 602.19, 13301.08),\n",
       " ('Channel Outlet', 550.39, None),\n",
       " ('Futuristic Bikes', 246.74, None),\n",
       " ('The Bicycle Accessories Company', 106.54, None),\n",
       " ('West Side Mart', 78.81, 317.0),\n",
       " ('Essential Bike Works', 40.9, None)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CompanyName, SubTotal, SUM(Weight*OrderQty) Weight\n",
    "  FROM SalesOrderHeader JOIN SalesOrderDetail ON (\n",
    "    SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID) JOIN\n",
    "    Product ON (Product.ProductID=SalesOrderDetail.ProductID) JOIN\n",
    "    CustomerAW ON (CustomerAW.CustomerID=SalesOrderHeader.CustomerID)\n",
    "    GROUP BY CompanyName, SubTotal\n",
    "    ORDER BY SubTotal DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "How many products in ProductCategory 'Cranksets' have been sold to an address in 'London'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) n\n",
    "  FROM Product JOIN ProductCategory ON (\n",
    "    Product.ProductCategoryID=ProductCategory.ProductCategoryID) JOIN\n",
    "    SalesOrderDetail ON (\n",
    "        SalesOrderDetail.ProductID=Product.ProductID) JOIN\n",
    "    SalesOrderHeader ON (\n",
    "        SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID) JOIN\n",
    "    CustomerAW ON (\n",
    "        CustomerAW.CustomerID=SalesOrderHeader.CustomerID) JOIN\n",
    "    CustomerAddress ON (\n",
    "        CustomerAW.CustomerID=CustomerAddress.CustomerID) JOIN\n",
    "    Address ON (CustomerAddress.AddressID=Address.AddressID)\n",
    "    WHERE ProductCategory.name='Cranksets' AND Address.City='London'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
